package com.gljx.web.controller;

import com.gljx.constant.ActivitiKey;
import com.gljx.util.*;
import com.gljx.web.Entity.*;
import com.gljx.web.dao.StudentMapper;
import com.gljx.web.service.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;
@Controller
@RequestMapping("/")
public class SubjectTwoRegConreoller {
	//自动注入
	@Autowired
	private DepartmentService departmentService;

	@Autowired
	private CustomLogService customLogService;
	
	@Autowired
	private ExamsubscribeService examsubscribeService;
	
	@Autowired
	private SubjectTwoRegService subjectTwoRegService;
	
	@Autowired
	private SubjectTwoRegentryService  subjectTwoRegentryService;
	
	@Autowired
	private StudentService studentService;
	
	@Autowired
	private StudentMapper studentMapper;

	@Autowired
	private MessageService messageService;

	@Autowired
	private HttpSession session;

	//导入activiti工具
	ActitiviUtil activitiUtil = new ActitiviUtil();
	
	/**
	 * 查询科目二考试登记信息
	 * @param request
	 * @param fnumber
	 * @param fdescription
	 * @param cfstudentname
	 * @param beginTime
	 * @param endTime
	 * @return
	 * @throws Exception
	 */
	@RequestMapping(value="/querySubjectTwoReg/look.action")
	public String querySubjectTwoReg(HttpServletRequest request, @RequestParam(value = "fnumber", defaultValue = "") String fnumber
            , @RequestParam(value = "fdescription", defaultValue = "") String fdescription
            , @RequestParam(value = "cfstudentname", defaultValue = "") String cfstudentname
            , @RequestParam(value = "beginTime", defaultValue = "") String beginTime
            , @RequestParam(value = "endTime", defaultValue = "") String endTime
			, @RequestParam(value = "type",defaultValue = "") String type
			, @RequestParam(value = "selectState",required = false,defaultValue = "-1") String selectState)throws Exception{
		
		PageBean<SubjectTwoReg> pb = new PageBean<>();
        //获取操作人信息
        User user = (User) session.getAttribute("user");
        try {
            fnumber = fnumber.trim();//去空格
            fdescription = fdescription.trim();
            cfstudentname = cfstudentname.trim();
            beginTime=beginTime.trim();
            endTime=endTime.trim();
			if("1".equals(selectState)){//如果传过来的标记为1 所以业务日期为空默认查询全部
				pb=getPageBean(request,user.getFcompanyid(),fnumber,fdescription,cfstudentname,null,null);
			}else{
				//默认时间
				if ("".equals(beginTime) && "".equals(endTime)) {
					beginTime = TimeFormat.getMonth();
					endTime = TimeFormat.getNowTime();
				}
				//获取分页
				pb=getPageBean(request,user.getFcompanyid(),fnumber,fdescription,cfstudentname,beginTime,endTime);
			}
            //把获取到的数据传回页面
            if(cfstudentname.equals("")){
            	cfstudentname=null;
            }
            //计数器
            int i = 0;
            //缓存需要删除的序列
            List<Integer> tem = new ArrayList<>();
            //获取当前分页的值
            List<SubjectTwoReg> subjectTwoRegList = pb.getBeanList();          
            
            for (SubjectTwoReg subjectTwoReg : subjectTwoRegList) {          	
                //查询学员信息
            	subjectTwoReg.setSubjectTwoRegentryList(subjectTwoRegentryService.selSubjectTwoRegentryByParentId(subjectTwoReg.getFid(),cfstudentname));              
                i++;
                //如果子类为空则记住该序列序号
                if (subjectTwoReg.getSubjectTwoRegentryList().size() ==0) {
                    tem.add(i);
                }
            }

            //进行集合删除
            for (int j = 0; j < tem.size(); j++) {
            	subjectTwoRegList.remove(tem.get(j) - j - 1);
            }
			//生成信息总数
			String messageAmount="共有"+pb.getTr()+"条信息";
			//返回界面的搜索条件
			request.setAttribute("messageAmount",messageAmount);
            request.setAttribute("fnumber", fnumber);
            request.setAttribute("fdescription", fdescription);
            request.setAttribute("cfstudentname", cfstudentname);
            request.setAttribute("beginTime", beginTime);
            request.setAttribute("endTime", endTime);
            request.setAttribute("subjectTwoRegList", subjectTwoRegList);
            request.setAttribute("pb", pb);
            request.setAttribute("type",type);
			request.setAttribute("selectState",selectState);
			request.setAttribute("activitiBPMN_KEY",ActivitiKey.Driving_GL_ID.equals(user.getFcompanyid()) ? ActivitiKey.SUBJECTTWOREG_KEY_GL : ActivitiKey.SUBJECTTWOREG_KEY_NC);
		} catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
		return"backHome/examManage/subjectTwoReg/subjectTwoRegList";
	}
	/**
	 * 跳转到增加科目二考试登记页面
	 * @param request
	 * @return
	 * @throws Exception
	 */
	@RequestMapping(value="/intoAddSubjectTwoReg")
	public String intoAddSubjectTwoReg(HttpServletRequest request,String type)throws Exception{
		//获取操作人信息
	    User user = (User)session.getAttribute("user");
	    //获取部门
	    List<Department> departments = departmentService.queryAdminByController(user.getFcompanyid());
	    //获取组织
	    Department department = departmentService.queryAdminByFid(user.getFcompanyid());
	    /*获取当前时间*/
        String createTime = TimeFormat.timeFormat(new Date());
	    //获取上一个编码
        /*String number = subjectTwoRegService.selectFnumber(user.getFcompanyid());*/
        //获取编码
        String fnumber = GetFnumber.fnumbers("TR");
        Calendar ca = Calendar.getInstance();// 得到一个Calendar的实例
        ca.setTime(new Date()); // 设置时间为当前时间
        ca.add(Calendar.MONTH, -3);// 月份减1
        Date resultDate = ca.getTime(); // 结果
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		//业务时间
		String time = sdf.format(new Date());
        String beginTime = sdf.format(resultDate);
        String endTime = sdf.format(new Date());
        request.setAttribute("beginTime", beginTime);
		request.setAttribute("beginTime", beginTime);
        request.setAttribute("endTime", endTime);
        request.setAttribute("fnumber", fnumber);
        request.setAttribute("department", department);
        request.setAttribute("createTime", createTime);
        request.setAttribute("user", user);
		request.setAttribute("type",type);
		request.setAttribute("time",time);
		return"backHome/examManage/subjectTwoReg/addSubjectTwoReg";
	}
	
	/**
	 * 增加科目二考试登记信息
	 * @param request
	 * @param subjectTwoReg
	 * @return
	 * @throws Exception
	 */
	@RequestMapping(value="/addSubjectTwoReg")
	public String addSubjectTwoReg(HttpServletRequest request,SubjectTwoReg subjectTwoReg)throws Exception{
		String [] subfid=request.getParameterValues("subfid");//学员id
		String [] subfseq=request.getParameterValues("subfseq");//序号
		String [] subcfzkzmbh=request.getParameterValues("subcfzkzmbh");//准考证明编号
		String [] subfnameL2=request.getParameterValues("subfnameL2");//学员名称
		String [] substufnumber=request.getParameterValues("substufnumber");//学员编号
		String [] subfnumber=request.getParameterValues("subfnumber");//编号
		String [] subcfidcardno=request.getParameterValues("subcfidcardno");//身份证号
		String [] subcfsex=request.getParameterValues("subcfsex");//性别
		for (int i=0;i<subcfsex.length;i++) {
			if(subcfsex[i].equals("男")){
				subcfsex[i]="1";
			}else {
				subcfsex[i]="2";
			}
		}
		String [] subcfsjhm=request.getParameterValues("subcfsjhm");//手机号码
		String [] subcflxdh=request.getParameterValues("subcflxdh");//联系号码
		String [] subcftjrName=request.getParameterValues("subcftjrName");//推荐人
		String [] cfsqzjcxdh=request.getParameterValues("cfsqzjcxdh");//申请准驾车型代号	
		String [] subcfjiaolianid=request.getParameterValues("subcfjiaolianid");//教练id
		String [] subjlname =request.getParameterValues("subjlname");//教练
		String [] subcfchexing=request.getParameterValues("subcfchexing");//车型
		String [] subcflocal=request.getParameterValues("subcflocal");//本地异地
		for (int i = 0; i < subcflocal.length; i++) {
			if(subcflocal[i].equals("本地")){
				subcflocal[i]="1";
			}else if(subcflocal[i].equals("异地")){
				subcflocal[i]="2";
			}else{
				subcflocal[i]="3";
			}
		}
		String [] subcfkscs=request.getParameterValues("subcfkscs");//考试次数
		String [] subcfckbk=request.getParameterValues("subcfckbk");//初考补考
		for (int i = 0; i < subcfckbk.length; i++) {
			if(subcfckbk[i].equals("初考")){
				subcfckbk[i]="1";
			}else{
				subcfckbk[i]="2";
			}
		}
		String [] subcfbxwx=request.getParameterValues("subcfbxwx");//本校外校
		for (int i = 0; i < subcfbxwx.length; i++) {
			if(subcfbxwx[i].equals("本校")){
				subcfbxwx[i]="1";
			}else{
				subcfbxwx[i]="2";
			}
		}
		String [] subcfkmyjgsj=request.getParameterValues("subcfkmyjgsj");//科目一合格时间
		String [] subcfbjgsj=request.getParameterValues("subcfbjgsj");//不及格时间
		String [] subcfscts=request.getParameterValues("subcfscts");//据不及格天数
		String [] subcfclazz=request.getParameterValues("subcfclazz");//班级
		String [] subcfysje=request.getParameterValues("subcfysje");//应收金额
		String [] subcfyhje=request.getParameterValues("subcfyhje");//优惠金额
		String [] subcfyisje=request.getParameterValues("subcfyisje");//应收金额
		String [] subcfqfje=request.getParameterValues("subcfqfje");//欠费金额
		String [] subcfremark =request.getParameterValues("subcfremark");//备注
		String [] subcfdajj=request.getParameterValues("subcfdajj");//档案交接
		String [] subcfjnbjj=request.getParameterValues("subcfjnbjj");//技能表交接
		//增加父类
		/*获取uuid*/
        String uuid = GetFid.UUIDIntoFid();
        //获取操作人信息
        User user = (User) session.getAttribute("user");
        subjectTwoReg.setFid(uuid);
        //公司id
        subjectTwoReg.setCfficompanyid(user.getFcompanyid());
        //控制单元id
        subjectTwoReg.setFcontrolunitid(user.getFcompanyid());
        //获取当前修改时间
        String createTime = TimeFormat.timeFormat(new Date());
        //修改时间
        subjectTwoReg.setFcreatetime(createTime);
        //修改人id
        subjectTwoReg.setFcreatorid(user.getFid());
        //增加
        int row =subjectTwoRegService.addSubjectTwoReg(subjectTwoReg);
        //增加子类
        for (int i = 0; i < subfid.length; i++) {
        	SubjectTwoRegentry subjectTwoRegentry=new SubjectTwoRegentry();
        	subjectTwoRegentry.setFid(GetFid.UUIDIntoFid());
        	subjectTwoRegentry.setFseq(Long.valueOf(subfseq[i]));
        	subjectTwoRegentry.setFparentid(subjectTwoReg.getFid());
        	subjectTwoRegentry.setCfstudentid(subfid[i]);
        	subjectTwoRegentry.setCfzkzmbh(subcfzkzmbh[i]);
        	subjectTwoRegentry.setStudentName(subfnameL2[i]);
        	subjectTwoRegentry.setCfstudentno(substufnumber[i]);
        	subjectTwoRegentry.setCfidcardno(subcfidcardno[i]);
        	subjectTwoRegentry.setCfsex(Long.valueOf(subcfsex[i]));
        	subjectTwoRegentry.setCfmobile(subcfsjhm[i]);
        	subjectTwoRegentry.setCfphone(subcflxdh[i]);
        	subjectTwoRegentry.setCfjsr(subcftjrName[i]);
        	subjectTwoRegentry.setCfsqzjcxdh(cfsqzjcxdh[i]);
        	subjectTwoRegentry.setCfjiaolianid(subcfjiaolianid[i]);
        	subjectTwoRegentry.setJlzname(subjlname[i]);
        	subjectTwoRegentry.setCfchexing(subcfchexing[i]);
        	subjectTwoRegentry.setCfbdyd(Long.valueOf(subcflocal[i]));
        	subjectTwoRegentry.setCfkscs(Long.valueOf(subcfkscs[i]));
        	subjectTwoRegentry.setCfckbk(subcfckbk[i]);
        	subjectTwoRegentry.setCfbxwx(subcfbxwx[i]);
        	subjectTwoRegentry.setCfkmyjgsj(subcfkmyjgsj[i]);
        	subjectTwoRegentry.setCfbjgsj(subcfbjgsj[i]);
        	subjectTwoRegentry.setCfscts(Long.valueOf(subcfscts[i]));
        	subjectTwoRegentry.setCfclazz(subcfclazz[i]);
        	subjectTwoRegentry.setCfysje(new BigDecimal(subcfysje[i]));
        	subjectTwoRegentry.setCfyhje(new BigDecimal(subcfyhje[i]));
        	subjectTwoRegentry.setCfyisje(new BigDecimal(subcfyisje[i]));
        	subjectTwoRegentry.setCfqfje(new BigDecimal(subcfqfje[i]));
        	subjectTwoRegentry.setCfremark(subcfremark[i]);
        	row +=subjectTwoRegentryService.addSubjectTwoRegentry(subjectTwoRegentry);
			//添加学员服务日志
			customLogService.addLog("考试管理",  "新增了学员:"+subjectTwoRegentry.getCfstudentno()+subjectTwoRegentry.getStudentName()+
					",科目二考试登记!车型:"+subjectTwoRegentry.getCfsqzjcxdh()+",教练:"+subjectTwoRegentry.getJlzname()+",考试日期:"+subjectTwoReg.getFbizdate()+",单据编号:"+subjectTwoReg.getFnumber(), subjectTwoRegentry.getCfstudentid(), user.getFid());
		}
		String result = "0";
		if(row == subfid.length+1){
			result = "1";
		}
        //添加日志
        customLogService.addLog("考试管理", TimeFormat.timeFormat(new Date()) + "科目二考试登记进行增加", null, user.getFid());

		/*return "redirect:/DrivingSys/addSubjectTwoReg";*/
		return "redirect:/DrivingSys/intoUpdateSubjectTwoReg?type="+result+"&fid="+uuid;
	}
	
	/**
	 * 跳转至科目二考试登记修改页面
	 * @param request
	 * @param fid
	 * @return
	 * @throws Exception
	 */
	@RequestMapping(value="/intoUpdateSubjectTwoReg")
	public String intoUpdateSubjectTwoReg(HttpServletRequest request,@RequestParam(value="fid",required=false)String fid)throws Exception{
		//获取操作人信息
	    User user = (User)session.getAttribute("user");
	    //获取部门
	    List<Department> departments = departmentService.queryAdminByController(user.getFcompanyid());
	    //获取组织
	    Department department = departmentService.queryAdminByFid(user.getFcompanyid());
        //查找父类
        SubjectTwoReg subjectTwoReg=subjectTwoRegService.selSubjectTwoRegByFid(fid);
        if(subjectTwoReg == null){
			return "/error/not_data";//单据不存在则跳转到错误页面
		}
        //查找子类
        subjectTwoReg.setSubjectTwoRegentryList(subjectTwoRegentryService.selSubjectTwoRegentryByParentId(fid, null));
		//获取当前时间
		Calendar now = Calendar.getInstance();
		now.add(Calendar.MONTH, -3);// 月份减1
         /*获取当前时间*/
		Date resultDate = now.getTime(); // 结果
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String beginTime = sdf.format(resultDate);
		String endTime = sdf.format(new Date());
		/*//查询该单据是否之前有提交过的流程历史记录
		String result = activitiUtil.getTaskStateByUser(
				ActivitiKey.SUBJECTTWOREG_KEY,
				subjectTwoReg.getFnumber()                  //单据
		);
		if(activitiUtil.notTask.equals(result))
		{
			System.out.println("该单据没有对应流程");        //没有历史流程（从来没有过流程的“新单据”）
			request.setAttribute("historyActiviti","no");
		}
		else if(activitiUtil.FINISH.equals(result))
		{
			System.out.println("流程已结束");                //只有有历史流程
			request.setAttribute("historyActiviti","yes");
		}//剩下两种是该任务进行中
		else if(activitiUtil.notAssign.equals(result))
		{
			System.out.println("经办人不匹配");               //正在进行的流程，但是经办人跟当前用户不匹配
			request.setAttribute("historyActiviti","yes");
		}
		else if(activitiUtil.ISPOWER.equals(result))
		{
			System.out.println("当前用户可以审核该单据");
			request.setAttribute("historyActiviti","yes");
			*//**正在进行的流程，且当前用户能够审核
			 * 如果是进入可提交的详情页面，这个标记表示
			 * 此流程进行中，即用户点击提交只需推进流程即可
			 *//*
		}*/
        request.setAttribute("activitiState",subjectTwoRegService.selectAuditPerson(user,subjectTwoReg.getFnumber()));
        request.setAttribute("activitiBPMN_KEY",ActivitiKey.Driving_GL_ID.equals(user.getFcompanyid()) ? ActivitiKey.SUBJECTTWOREG_KEY_GL : ActivitiKey.SUBJECTTWOREG_KEY_NC);
		request.setAttribute("beginTime", beginTime);
		request.setAttribute("endTime", endTime);
		request.setAttribute("department",department);
        request.setAttribute("subjectTwoReg", subjectTwoReg);
		return "backHome/examManage/subjectTwoReg/updateSubjectTwoReg";
	}
	/**
	 * 修改科目二考试登记信息
	 * @param request
	 * @param subjectTwoReg
	 * @return
	 * @throws Exception
	 */
	@RequestMapping(value="/updateSubjectTwoReg")
	public String updateSubjectTwoReg(HttpServletRequest request,SubjectTwoReg subjectTwoReg)throws Exception{
		String [] subfid=request.getParameterValues("subfid");//学员id
		String [] subfseq=request.getParameterValues("subfseq");//序号
		String [] subcfzkzmbh=request.getParameterValues("subcfzkzmbh");//准考证明编号
		String [] subfnameL2=request.getParameterValues("subfnameL2");//学员名称
		String [] substufnumber=request.getParameterValues("substufnumber");//学员编号
		String [] subfnumber=request.getParameterValues("subfnumber");//编号
		String [] subcfidcardno=request.getParameterValues("subcfidcardno");//身份证号
		String [] subcfsex=request.getParameterValues("subcfsex");//性别
		for (int i=0;i<subcfsex.length;i++) {
			if(subcfsex[i].equals("男")){
				subcfsex[i]="1";
			}else {
				subcfsex[i]="2";
			}
		}
		String [] subcfsjhm=request.getParameterValues("subcfsjhm");//手机号码
		String [] subcflxdh=request.getParameterValues("subcflxdh");//联系号码
		String [] subcftjrName=request.getParameterValues("subcftjrName");//推荐人
		String [] cfsqzjcxdh=request.getParameterValues("cfsqzjcxdh");//申请准驾车型代号	
		String [] subcfjiaolianid=request.getParameterValues("subcfjiaolianid");//教练id
		String [] subjlname =request.getParameterValues("subjlname");//教练
		String [] subcfchexing=request.getParameterValues("subcfchexing");//车型
		String [] subcflocal=request.getParameterValues("subcflocal");//本地异地
		for (int i = 0; i < subcflocal.length; i++) {
			if(subcflocal[i].equals("本地")){
				subcflocal[i]="1";
			}else if(subcflocal[i].equals("异地")){
				subcflocal[i]="2";
			}else{
				subcflocal[i]="3";
			}
		}
		String [] subcfkscs=request.getParameterValues("subcfkscs");//考试次数
		String [] subcfckbk=request.getParameterValues("subcfckbk");//初考补考
		for (int i = 0; i < subcfckbk.length; i++) {
			if(subcfckbk[i].equals("初考")){
				subcfckbk[i]="1";
			}else{
				subcfckbk[i]="2";
			}
		}
		String [] subcfbxwx=request.getParameterValues("subcfbxwx");//本校外校
		for (int i = 0; i < subcfbxwx.length; i++) {
			if(subcfbxwx[i].equals("本校")){
				subcfbxwx[i]="1";
			}else{
				subcfbxwx[i]="2";
			}
		}
		String [] subcfkmyjgsj=request.getParameterValues("subcfkmyjgsj");//科目一合格时间
		String [] subcfbjgsj=request.getParameterValues("subcfbjgsj");//不及格时间
		String [] subcfscts=request.getParameterValues("subcfscts");//据不及格天数
		String [] subcfclazz=request.getParameterValues("subcfclazz");//班级
		String [] subcfysje=request.getParameterValues("subcfysje");//应收金额
		String [] subcfyhje=request.getParameterValues("subcfyhje");//优惠金额
		String [] subcfyisje=request.getParameterValues("subcfyisje");//应收金额
		String [] subcfqfje=request.getParameterValues("subcfqfje");//欠费金额
		String [] subcfremark =request.getParameterValues("subcfremark");//备注
		String [] subcfdajj=request.getParameterValues("subcfdajj");//档案交接
		String [] subcfjnbjj=request.getParameterValues("subcfjnbjj");//技能表交接
		//修改父类
		//获取操作人信息
        User user = (User) session.getAttribute("user");
        //获取当前修改时间
        String updateTime = TimeFormat.timeFormat(new Date());
        //把创建时间和业务日期清空不让修改
        subjectTwoReg.setFcreatetime(null);
        //subjectTwoReg.setFbizdate(null);
        int number=subjectTwoRegentryService.selSubjectTwoRegentryByParentId(subjectTwoReg.getFid(),null).size();
        int count=0;//记录sql语句执行成功的条数
        count=subjectTwoRegService.updateSubjectTwoReg(user, subjectTwoReg, subfid, subfseq, subcfzkzmbh, subfnameL2, substufnumber, subfnumber, subcfidcardno, subcfsex, 
    		   		subcfsjhm, subcflxdh, subcftjrName, cfsqzjcxdh, subcfjiaolianid, subjlname,subcfchexing, subcflocal, subcfkscs, subcfckbk, subcfbxwx, subcfkmyjgsj, subcfbjgsj,
    		   		subcfscts, subcfclazz, subcfysje, subcfyhje, subcfyisje, subcfqfje,subcfremark);
		String result="";
        if(count == subfid.length+number+1){//所有操作成功跳转页面
			result="1";

			//添加日志
	        customLogService.addLog("考试管理", TimeFormat.timeFormat(new Date()) + "科目二考试登记进行修改", null, user.getFid());
			return "redirect:/DrivingSys/querySubjectTwoReg/look.action?type="+result;
		}
        return "error";
	}
	
	/**
	 * 根据fid删除科目二登记信息
	 * @param request
	 * @param deleteId
	 * @param fid
	 * @return
	 * @throws Exception
	 */
	@RequestMapping(value="/deleteSubjectTwoRegByFid")
	public String deleteSubjectTwoRegByFid(HttpServletRequest request, String[] deleteId,String fid)throws Exception{
		User user = (User) session.getAttribute("user");
        //删除记录
		if(fid==null || fid.equals("")){
			for (int i = 0; i < deleteId.length; i++) {
	            /*过滤为空的删除操作*/
	            if (!deleteId[i].equals("")&&deleteId[i]!=null) {
	            	//根据fid查找单据编号
					SubjectTwoReg subjectTwoReg =subjectTwoRegService.selSubjectTwoRegByFid(deleteId[i]);
					//删除历史流程或者进行中的流程
					activitiUtil.deleteTask(ActivitiKey.Driving_GL_ID.equals(user.getFcompanyid()) ? ActivitiKey.SUBJECTTWOREG_KEY_GL : ActivitiKey.SUBJECTTWOREG_KEY_NC,subjectTwoReg.getFnumber());
	            	subjectTwoRegService.deleteByPrimaryKey(deleteId[i]);//删除父类
	            	subjectTwoRegentryService.deleteByParentId(deleteId[i],user,subjectTwoReg.getFbizdate(),(String)subjectTwoReg.getFnumber());//删除子类
	            }
	        }	
		}else{
			//根据fid查找单据编号
			SubjectTwoReg subjectTwoReg =subjectTwoRegService.selSubjectTwoRegByFid(fid);
			//删除历史流程或者进行中的流程
			activitiUtil.deleteTask(ActivitiKey.Driving_GL_ID.equals(user.getFcompanyid()) ? ActivitiKey.SUBJECTTWOREG_KEY_GL : ActivitiKey.SUBJECTTWOREG_KEY_NC,subjectTwoReg.getFnumber());
			subjectTwoRegService.deleteByPrimaryKey(fid);//删除父类
			subjectTwoRegentryService.deleteByParentId(fid,user,subjectTwoReg.getFbizdate(),(String)subjectTwoReg.getFnumber());//删除子类】
        }
		//添加日志
        customLogService.addLog("考试管理", TimeFormat.timeFormat(new Date()) + "科目二考试登记进行删除", null, user.getFid());
		return "redirect:/DrivingSys/querySubjectTwoReg/look.action";
	}
	
	/**
	 * 根据考试日期和车型查找科目二考试预约学员
	 * @return
	 * @throws Exception
	 */
	@RequestMapping(value="/selExamsubscribeTwoByExamdateAndCarType")
	@ResponseBody
	public Object selExamsubscribeTwoByExamdateAndCarType(HttpServletRequest request,String examdate,String carType)throws Exception{
		User user = (User) session.getAttribute("user");
		String [] ids;
		String [] lcsjids;
		List<SubjectTwoRegentry> subjectTwoRegentryList=new ArrayList<>();		
		List<Examsubscribe> examsubscribeList=null;
		examsubscribeList=examsubscribeService.selExamsubcribeByFbizdate(user.getFcompanyid(),"3",examdate,carType);
		if(examsubscribeList == null){//如果等于null则说明选中的考试日期当天没有预约的学员
			return examsubscribeList;
		}
		//防止错误操作生产相同的数据,去除重复数据
		for (int i = 0; i < examsubscribeList.size() ; i++) {
			for (int j = examsubscribeList.size() -1; j > i ; j--) {
				if(examsubscribeList.get(i).getCfstudentid().equals(examsubscribeList.get(j).getCfstudentid())){
					examsubscribeList.remove(j);
				}
			}
		}
		// 根据学员id去学员信息里面查找练车时间id
		ids = new String[examsubscribeList.size()];
		lcsjids = new String[examsubscribeList.size()];
		for (int i = 0; i < examsubscribeList.size(); i++) {
			Student student = new Student();
			student = studentMapper.selectByPrimaryKey(examsubscribeList.get(i).getCfstudentid());
			ids[i] = examsubscribeList.get(i).getCfstudentid();			
			lcsjids[i] = student.getCfkmelcsjid();
		}
		//否则查找出需要考试学员的信息
		if(ids != null ){
			for (int i = 0; i < ids.length; i++) {
				//System.out.println(ids[i]+" "+lcsjids[i]);
				if(subjectTwoRegentryService.selChangeCarTwoRegBySutid(lcsjids[i], ids[i])!=null){
					//去换教练查找信息
					subjectTwoRegentryList.add(subjectTwoRegentryService.selChangeCarTwoRegBySutid(lcsjids[i], ids[i]));	
				}else{
					//这一步是为了防止出现科目二分车的单据在使用过程中被反审核，然后又要需要到这条单据这里做出提到到前台，以便提示用户
					if(subjectTwoRegentryService.selSharCarTwoRegBySutid(lcsjids[i], ids[i])==null){
						return -1;//数据不匹配
					}
					//去分车找信息
					subjectTwoRegentryList.add(subjectTwoRegentryService.selSharCarTwoRegBySutid(lcsjids[i], ids[i]));
				}									
			}
		}
		return subjectTwoRegentryList;
	}
	
	/**
	 * 根据fid查找科目二考试登记子表信息 包括教练信息 和练车车牌
	 * @return
	 * @throws Exception
	 */
	@RequestMapping(value="/selSubjectTwoRegentryBysutid")
	@ResponseBody
	public Object selSubjectTwoRegentry(@RequestParam(value = "id", required = false) String id,
			@RequestParam(value = "lcsjids", required = false) String lcsjid)throws Exception{
		String [] ids=id.split(",");
		String [] lcsjids=lcsjid.split(",");		
		List<SubjectTwoRegentry> subjectTwoRegentryList=new ArrayList<>();
		for (int i = 0; i < ids.length; i++) {			
			if(subjectTwoRegentryService.selChangeCarTwoRegBySutid(lcsjids[i], ids[i])!=null){
				subjectTwoRegentryList.add(subjectTwoRegentryService.selChangeCarTwoRegBySutid(lcsjids[i], ids[i]));
				//System.out.println("换教练后的名称: "+subjectTwoRegentryList.get(i).getJlzname());
			}else{
				//这一步是为了防止出现科目二分车的单据在使用过程中被反审核，然后又要需要到这条单据这里做出提到到前台，以便提示用户
				if(subjectTwoRegentryService.selSharCarTwoRegBySutid(lcsjids[i], ids[i])==null){
					return -1;//数据不匹配
				}
				//System.out.println(" 练车时间id: "+lcsjids[i]+" 学员id:"+ ids[i]);
				subjectTwoRegentryList.add(subjectTwoRegentryService.selSharCarTwoRegBySutid(lcsjids[i], ids[i]));
			}			
		}
		return subjectTwoRegentryList;  
	}

	/**
	 *  提交科目二考试登记单据到activiti
	 * @throws Exception
	 */
	@RequestMapping(value = "submitSubjectTwoRegActiviti")
	@ResponseBody
	public Object submitSubjectTwoRegActiviti(HttpServletRequest request,String fnumber,String fid)throws Exception{
		User user = (User) session.getAttribute("user");
		//查找父类
		SubjectTwoReg _subjectTwoReg=subjectTwoRegService.selSubjectTwoRegByFid(fid);
		//查找子类
		 List<SubjectTwoRegentry>  subjectTwoRegentries= subjectTwoRegentryService.selSubjectTwoRegentryByParentId(fid, null);
		String result = "";
		//2.将这个单据编号和你需要保存的流程变量存入map集合中
		Map<String,Object> map = new HashMap<String,Object>();
		if(fnumber != null || !"".equals(fnumber)){
			map.put(ActivitiKey.Driving_GL_ID.equals(user.getFcompanyid()) ? ActivitiKey.SUBJECTTWOREG_KEY_GL : ActivitiKey.SUBJECTTWOREG_KEY_NC,fnumber);
		}else{
			result ="failed";
		}
		//提交之前先判断该单据是否之前有流程历史记录
		String historyRecord =activitiUtil.getTaskStateByUser(
				ActivitiKey.Driving_GL_ID.equals(user.getFcompanyid()) ? ActivitiKey.SUBJECTTWOREG_KEY_GL : ActivitiKey.SUBJECTTWOREG_KEY_NC,
				fnumber);
		if(historyRecord.equals("notAssign") || historyRecord.equals("power")){//如果之前有流程就不需要提交
			System.out.println("之前有正在进行的流行实例!");
			//只需要推进流程即可
			Message message1=activitiUtil.complteFlowByVariable(
					ActivitiKey.Driving_GL_ID.equals(user.getFcompanyid()) ? ActivitiKey.SUBJECTTWOREG_KEY_GL : ActivitiKey.SUBJECTTWOREG_KEY_NC,  //指定要启动的流程定义
					fnumber                     //流程变量
			);
			if(message1 !=null){
				message1.setMessage("科目二考试登记 审核 #"+fnumber+"# ");//消息内容
				message1.setUrl("/intoUpdateSubjectTwoReg?fid="+fid);//详细路径
				message1.setEditUrl("/checkSubjectTwoRegActiviti?fid=" + fid+"&fnumber=" + fnumber);
				messageService.insert(message1);
			}
		}else{
			//否则作提交操作
			//3.调用方法
			String piID = activitiUtil.creatFlow(
					ActivitiKey.Driving_GL_ID.equals(user.getFcompanyid()) ? ActivitiKey.SUBJECTTWOREG_KEY_GL : ActivitiKey.SUBJECTTWOREG_KEY_NC,  //指定要启动的流程定义
					map                     //流程变量
			);
			Message message2=activitiUtil.completeFlowByPiID(piID);
			if(message2 !=null){
				message2.setMessage("科目二考试登记 审核 #"+fnumber+"# ");//消息内容
				message2.setUrl("/intoUpdateSubjectTwoReg?fid="+fid);//详细路径
				message2.setEditUrl("/checkSubjectTwoRegActiviti?fid=" + fid+"&fnumber=" + fnumber);
				messageService.insert(message2);
			}
		}
		SubjectTwoReg subjectTwoReg =new SubjectTwoReg();
		subjectTwoReg.setFnumber(fnumber);
		subjectTwoReg.setFid(fid);
		subjectTwoReg.setCfbillstate(4L);//提交状态
		//修改
		int row =subjectTwoRegService.updateSubjectTwoReg(subjectTwoReg);
		if(subjectTwoRegentries !=null){
			for (int i = 0; i < subjectTwoRegentries.size(); i++) {
				customLogService.addLog("考试管理",  "提交了学员:"+subjectTwoRegentries.get(i).getCfstudentno()+subjectTwoRegentries.get(i).getStudentName()+
						",科目二考试登记!车型:"+subjectTwoRegentries.get(i).getCfsqzjcxdh()+",教练:"+subjectTwoRegentries.get(i).getJlzname()+",考试日期:"+_subjectTwoReg.getFbizdate()+",单据编号:"+_subjectTwoReg.getFnumber(), subjectTwoRegentries.get(i).getCfstudentid(), user.getFid());
			}
		}
		if(row > 0){
			result= "success";
		}
		Map<String,String> _map =new HashMap<>();
		_map.put("result",result);
		return _map;
	}

	/**
	 * 审核activiti里面的单据
	 * @param fid
	 * @param fnumber
	 * @return
	 * @throws Exception
	 */
	@RequestMapping(value = "checkSubjectTwoRegActiviti")
	@ResponseBody
	public Object checkSubjectTwoRegActiviti(HttpServletRequest request,String fid,String fnumber,
											 @RequestParam(defaultValue = "1", value = "agree")String agree,String auditcomment) throws Exception{
		//获取当前登录用户的信息
		User user =(User)session.getAttribute("user");
		//查找父类
		SubjectTwoReg _subjectTwoReg=subjectTwoRegService.selSubjectTwoRegByFid(fid);
		//查找子类
		List<SubjectTwoRegentry>  subjectTwoRegentries= subjectTwoRegentryService.selSubjectTwoRegentryByParentId(fid, null);
		String result = "failed";
		Integer agreesate=null;
		if(agree != null || !"".equals(agree)){
			agreesate=Integer.parseInt(agree);
		}
		//2.1审核有两种情况同意与不同意，不同意可能有批注
		String message = auditcomment;
		//2.2使用这个方法必须保证usertask节点与其子连线key值符合要求
		Object returnMsg =
				activitiUtil
						.auditComplteMessage(//新的方法，自定义流程不需要这个
								ActivitiKey.Driving_GL_ID.equals(user.getFcompanyid()) ? ActivitiKey.SUBJECTTWOREG_KEY_GL : ActivitiKey.SUBJECTTWOREG_KEY_NC,
								fnumber,                  //单据
								agreesate,                 //1、0是否同意取决于你的流程图
								message              //批注，直接把前端传过来的string塞进来
						);
		SubjectTwoReg subjectTwoReg =new SubjectTwoReg();
		subjectTwoReg.setFnumber(fnumber);
		subjectTwoReg.setFid(fid);
		//subjectTwoReg.setFauditorid(user.getFid());//设置审计人id
		if(returnMsg instanceof Message){
			Message resultMsg = (Message) returnMsg;
			resultMsg.setMessage("科目二考试登记-审核 #"+fnumber+"# ");//消息内容
			resultMsg.setUrl("/intoUpdateSubjectTwoReg?fid="+fid);//详细路径
			resultMsg.setEditUrl("/checkSubjectTwoRegActiviti?fid=" + fid+"&fnumber=" + fnumber);
			messageService.insert(resultMsg);

			result= "success";
			if(subjectTwoRegentries !=null){
				for (int i = 0; i < subjectTwoRegentries.size(); i++) {
					customLogService.addLog("考试管理",  "审核了学员:"+subjectTwoRegentries.get(i).getCfstudentno()+subjectTwoRegentries.get(i).getStudentName()+
							",科目二考试登记!车型:"+subjectTwoRegentries.get(i).getCfsqzjcxdh()+",教练:"+subjectTwoRegentries.get(i).getJlzname()+",考试日期:"+_subjectTwoReg.getFbizdate()+",单据编号:"+_subjectTwoReg.getFnumber(), subjectTwoRegentries.get(i).getCfstudentid(), user.getFid());
				}
			}
		}else
		if(activitiUtil.notAssign .equals(returnMsg)) {
			System.out.println("当前用户不是要求的经办人");
			result="isNoResponsiblePerson";
		}
		else if(activitiUtil.FINISH.equals(returnMsg)) {
			System.out.println("此流程成功推进，并且此流程已经结束");
			subjectTwoReg.setFauditorid(user.getFid());//设置审计人id
			subjectTwoReg.setCfbillstate(5L);//审核状态
			//修改
			int row =subjectTwoRegService.updateSubjectTwoReg(subjectTwoReg);
			if(row >0){
				result= "successAndOver";
			}
		}
		else if(activitiUtil.SUCCESS.equals(returnMsg)) {
			System.out.println("此流程成功推进");
			result= "success";
		}
		else if(activitiUtil.notTask.equals(returnMsg)) {
			System.out.println("没有找到正在进行的相关流程");
		}
		if(agree.equals("2")){
			subjectTwoReg.setCfbillstate(3L);//保存状态
			//修改
			subjectTwoRegService.updateSubjectTwoReg(subjectTwoReg);
		}
		Map<String,String> _map =new HashMap<>();
		_map.put("result",result);
		return _map;
	}

	/**
	 * 反审核科目二单据
	 * @param request
	 * @param fid
	 * @param againstAuditcomment
	 * @return
	 * @throws Exception
	 *
	 */
	@RequestMapping(value = "againstCheckSubjectTwoRegActiviti")
	@ResponseBody
	public Object againstCheckSubjectTwoRegActiviti(HttpServletRequest request,String fid,String fnumber,String againstAuditcomment ,String activitiState)throws  Exception{
		//获取当前登录用户的信息
		User user =(User)session.getAttribute("user");
		String result ="falied";
		//SubjectTwoReg _subjectTwoReg=subjectTwoRegService.selSubjectTwoRegByFid(fid);
		if("1".equals(activitiState)){//当前有正在进行的流程
			Object _result = activitiUtil.jumpActivtiMessage(
					ActivitiKey.Driving_GL_ID.equals(user.getFcompanyid()) ? ActivitiKey.SUBJECTTWOREG_KEY_GL : ActivitiKey.SUBJECTTWOREG_KEY_NC,
					fnumber,                  //单据
					againstAuditcomment
			);
			if(_result instanceof Message){
				Message resultMsg = (Message) _result;
				resultMsg.setMessage("科目二考试登记-反审核 #"+fnumber+"#");//消息内容
				resultMsg.setUrl("/intoUpdateSubjectTwoReg?fid="+fid);//详细路径
				messageService.insert(resultMsg);
			}
			else if(activitiUtil.SUCCESS.equals(_result))
			{	//查找父类
				SubjectTwoReg _subjectTwoReg=subjectTwoRegService.selSubjectTwoRegByFid(fid);
				//查找子类
				List<SubjectTwoRegentry>  subjectTwoRegentries= subjectTwoRegentryService.selSubjectTwoRegentryByParentId(fid, null);
				for (int i = 0; i < subjectTwoRegentries.size(); i++) {
					customLogService.addLog("考试管理",  "反审核了学员:"+subjectTwoRegentries.get(i).getCfstudentno()+subjectTwoRegentries.get(i).getStudentName()+
							",科目二考试登记!车型:"+subjectTwoRegentries.get(i).getCfsqzjcxdh()+",教练:"+subjectTwoRegentries.get(i).getJlzname()+",考试日期:"+_subjectTwoReg.getFbizdate()+",单据编号:"+_subjectTwoReg.getFnumber(), subjectTwoRegentries.get(i).getCfstudentid(), user.getFid());
				}
				System.out.println("成功!");
			}else if(activitiUtil.notActiviti.equals(result))
			{
				System.out.println("找不到流程节点");

			}else if(activitiUtil.notTask.equals(result))
			{
				System.out.println("没有找到任务");
			}
		}else{//当前没有进行的流程
			//直接修改单据状态为“保存”状态
			Message message2 = activitiUtil.getMessage(
					ActivitiKey.Driving_GL_ID.equals(user.getFcompanyid()) ? ActivitiKey.SUBJECTTWOREG_KEY_GL : ActivitiKey.SUBJECTTWOREG_KEY_NC,
					fnumber                  //单据
			);

			if(message2 != null)
			{
				message2.setMessage("科目二考试登记-反审核 #"+fnumber+"#");//消息内容
				message2.setUrl("/intoUpdateSubjectTwoReg?fid="+fid);//详细路径
				messageService.insert(message2);
			}
		}
		//将单据状态修改成保存状态3
		SubjectTwoReg subjectTwoReg = new SubjectTwoReg();
		subjectTwoReg.setFid(fid);
		subjectTwoReg.setCfbillstate(3L);//保存状态
		subjectTwoReg.setFauditorid(user.getFid());//设置反审计人id
		int row = subjectTwoRegService.updateSubjectTwoReg(subjectTwoReg);
		if (row > 0) {
			result = "success";
		}
		Map<String,String> _map =new HashMap<>();
		_map.put("result",result);
		return _map;
	}
	
	@SuppressWarnings("unchecked")
	@ResponseBody
	@RequestMapping(value = "/getPagExamSub")
	public Map getPageBean2(HttpServletRequest request, @RequestParam(defaultValue = "", value = "name") String name, @RequestParam(defaultValue = "") String cflxdh, @RequestParam(defaultValue = "0") String pindex, 
			String beginTime, String endTime,String examdate,String carType,String subjectType) throws Exception {
		Calendar ca = Calendar.getInstance();// 得到一个Calendar的实例
        ca.setTime(new Date()); // 设置时间为当前时间
        ca.add(Calendar.MONTH, -3);// 月份减1
        Date resultDate = ca.getTime(); // 结果
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        /*if(beginTime.equals("") || beginTime ==null){
        	 beginTime = sdf.format(resultDate);
        }
        if(endTime.equals("") || endTime==null){
        	 endTime = sdf.format(new Date());
        }*/
	        String psql = "select "
	        		+ "to_char(t.FCREATETIME,'YYYY-MM-DD HH24:MI:SS') fcreatetime,"
	                + "to_char(t.FLASTUPDATETIME,'YYYY-MM-DD HH24:MI:SS') flastupdatetime,"
	                + "to_char(t.CFRXSJ,'yyyy-MM-dd HH24:MI:SS')  cfrxsj,"
	                + "to_char(t.CFKMYHERQ,'YYYY-MM-DD HH24:MI:SS')  cfkmyherq,"
	                + "to_char(t.CFBIRTHDAY,'YYYY-MM-DD') cfbirthdays,"
	                + "to_char(t.CFKMEHGRQ,'YYYY-MM-DD HH24:MI:SS') cfkmehgrq,"
	                + "to_char(t.CFKMSHGRQ,'YYYY-MM-DD HH24:MI:SS') cfkmshgrq,"
	                + "to_char(t.CFKMYBJGSJ,'YYYY-MM-DD HH24:MI:SS') cfkmybjgsj,"
	                + "to_char(t.CFKMSBJGSJ,'YYYY-MM-DD HH24:MI:SS') cfkmsbjgsj,"
	                + "to_char(t.CFKMEBJGSJ,'YYYY-MM-DD HH24:MI:SS') cfkmebjgsj,"
	                + "to_char(t.CFKMETLRQ,'YYYY-MM-DD HH24:MI:SS') cfkmetlrq,"
	                + "to_char(t.CFKMSTLRQ,'YYYY-MM-DD HH24:MI:SS') cfkmstlrq,"
	                + "to_char(t.CFMNKHGSJ,'YYYY-MM-DD HH24:MI:SS') cfmnkhgsj,"
	                + "to_char(t.CFMNKBJGRQ,'YYYY-MM-DD HH24:MI:SS') cfmnkbjgrq,"
	                + "to_char(t.CFKMEKSQKSJ,'YYYY-MM-DD HH24:MI:SS') cfkmeksqksj,"
	                + "to_char(t.CFMNKSJ,'YYYY-MM-DD HH24:MI:SS') cfmnksj ,"
	                + "t.FID, t.FNUMBER, t.FSIMPLENAME, t.FCREATORID, t.FLASTUPDATEUSERID, "
	                + "t.FCONTROLUNITID, t.CFFICOMPANYID, t.CFNATIONALITYID, t.CFIDCARDNO, t.CFNATION, "
	                + "t.CFADDRESS, t.CFLINKADDRESS, t.CFZZZH, t.CFSJHM, t.CFLXDH, t.CFYZBM, t.CFEMAIL, t.CFSLLX, t.CFXZJCXDH, "
	                + "t.CFSLZJCXDH, t.CFTJRID, t.CFCLAZZID, t.CFAMOUNT, t.CFLOCAL, t.CFTRAINTYPEID, t.CFREMARK, "
	                + "t.CFSEX, t.CFSTUDENTSTATE, t.CFTIMECARD, t.CFRECEIVED, t.CFARREARAGE, t.CFFAVORABLE, t.CFKMYKSS, "
	                + " t.CFKMEKSS, t.CFKMSKSS, t.CFDEPARTMENT, t.CFZKZMBH, t.CFDAZT, "
	                + "t.CFCFWZ, t.CFDESC, t.CFBXWX, t.CFJSYBH, t.CFBIMUDF0060, "
	                + " t.CFBIMUDF0061,  t.CFKMSTLCS, t.CFKMSTLYY, t.CFSHORTPINYIN, t.CFPINYIN, t.FNAME_L1, "
	                + "t.FNAME_L2, t.FDESCRIPTION_L1, t.FDESCRIPTION_L2, t.FDESCRIPTION_L3, t.CFJNBJJ, t.CFDAJJ, "
	                + " t.CFKMELCSJID, t.CFKMSLCSJID,  t.CFMNKCS, t.CFKMEQKCS, "
	                + " t.CFMNKFS, t.CFKMYQKCS, t.CFKMSQKCS,  t.CFYHJ, t.CFSNQL, "
	                + " t.CFWCQL, t.CFTRAINBESPEAKREMARK, t.CFEXAMBESPEAKREMARK, t.LNG, t.LAT, t.DISTRICT, t.CITY, t.PROVINCE,u.name as FCREATENAME,u1.name as FLASTUPDATENAME,u2.FNAME_L2 AS CFTJRNAME,C.FNAME_L2 AS CFCLAZZNAME,Y.FNAME_L2 AS CFTRAINTYPENAME"
	                + " ,E1.FNAME_L2 AS CFKMELCSJNAME,E2.FNAME_L2 AS CFKMSLCSJNAME ,A.FNAME_L2 AS cfficompanyName "
	                + "  FROM CT_DS_STUDENT T LEFT JOIN T_E_USER U ON T.FCREATORID=U.FID LEFT JOIN T_E_USER U1 ON T.FLASTUPDATEUSERID=U1.FID LEFT JOIN CT_DS_PERSON  u2 on t.cftjrid=u2.fid left join CT_DS_CLAZZ C ON T.cfclazzid=C.FID"
	                + " LEFT JOIN  CT_DS_TRAINTYPE Y ON T.CFTRAINTYPEID=Y.FID LEFT JOIN CT_DS_EXERCISECARPLAN E1 ON E1.FID=T.CFKMELCSJID LEFT JOIN CT_DS_EXERCISECARPLAN E2 ON E2.FID=T.CFKMSLCSJID LEFT JOIN CT_DS_ADMIN A ON T.CFFICOMPANYID=A.FID"
	                +"  WHERE T.FID IN (SELECT E.CFSTUDENTID from CT_DS_EXAMSUBSCRIBE E where  E.CFSUBSCRIBERESULT = 1  and E.CFBILLTYPE='"+subjectType+"' AND to_CHAR(E.CFEXAMDATE,'YYYY-MM-DD') = '"+examdate+"' and E.CFSQZJCXDH='"+carType+"')";
	        if (!"".equals(name)) {
	            psql += "AND t.FNAME_L2 LIKE '%" + name + "%' ";
	        }
	        if (!"".equals(cflxdh)) {
	            psql += "AND t.CFSJHM LIKE '%" + cflxdh + "%' ";
	        }
			if(beginTime != null && !"".equals(beginTime) && endTime != null && "".equals(endTime)){
				psql += "AND (t.CFRXSJ BETWEEN to_date('" + beginTime + "','YYYY/MM/DD') AND to_date('" + endTime + "','YYYY/MM/DD'))";
			}
			//System.out.println(psql);
			int psize = 50; //每页显示数
	        Map<String, Object> paramMap = new HashMap<String, Object>();
	        paramMap = studentService.selectPage(Integer.valueOf(pindex), psql, psize);
	        PageBean<Student> pb = new PageBean<>();
	        pb.setPc(Integer.valueOf(pindex)); //页码
	        Map<String, Object> paramMapCount = getPageBeanCount(psql); //取记录总数
	        pb.setTr((int) paramMapCount.get("Pcount")); //记录总数
	        pb.setPs(psize);
	        Map stu = new HashMap();
	        stu.put("sList", (List<Student>) paramMap.get("v_cur"));
	        stu.put("pb", (pb.getTr() / pb.getPs()));

	        return stu; //每页数据集;

	    }
	@SuppressWarnings("unchecked")
    /**
     * 返回PageBean实体类  包括记录总数，页码，每页记录集等  dmz 20160921
     * @return 返回PageBean实体类
     * @throws Exception
     */
    public PageBean<SubjectTwoReg> getPageBean(HttpServletRequest request, String companyId, String fnumber,
            String fdescription,String studentName,String beginTime, String endTime) throws Exception {

        int offset;//从第几条数据开始加载
        if (request.getParameter("pager.offset") == null) {
            offset = 0;
        } else {
            offset = Integer.parseInt(request.getParameter("pager.offset"));
        }
        //拼接SQL语句
        StringBuffer psql = new StringBuffer();
       
        	psql.append(" SELECT distinct S.FID,S.FCREATORID,S.FCREATETIME,S.FLASTUPDATEUSERID,S.FLASTUPDATETIME,S.FCONTROLUNITID, "+
        				"S.FNUMBER,to_char(S.FBIZDATE,'YYYY-MM-DD') FBIZDATE ,S.FHANDLERID,S.FDESCRIPTION,S.FAUDITORID,S.CFFICOMPANYID,S.CFBILLSTATE, "+
        				"S.CFCARTYPE,S.CFZBSYLT,S.CFZBSSTN,S.CFBSKYLT,S.CFBKSSTN,S.CFZBS,S.CFZBKS, E.NAME AS checkName,U.NAME AS createName "+
        				"FROM CT_DS_SUBJECTTWOREG S LEFT JOIN T_E_USER U ON U.FID=S.FCREATORID LEFT JOIN T_E_USER E ON E.FID=S.FAUDITORID "+
        				" LEFT JOIN CT_DS_SUBJECTTWOREGENTRY S1 ON S1.FPARENTID = S.FID "+
        				" LEFT JOIN CT_DS_STUDENT STU ON STU.FID = S1.CFSTUDENTID "+
        				"WHERE S.FCONTROLUNITID='"+companyId+"'");
        	if (fnumber == null || fnumber.equals("")) {
				
			} else {
				psql.append( "AND S.FNUMBER LIKE '%" + fnumber + "%' ");
			}
			if (fdescription == null || fdescription.equals("")) {
	
			} else {
				psql.append( "AND S.FDESCRIPTION LIKE '%" + fdescription + "%'");
			}
			if (studentName == null || studentName.equals("")) {

    		} else {
    			psql.append( "AND STU.FNAME_L2 LIKE '%" + studentName + "%'");
    		}
			if (beginTime == null || endTime == null) {
			/*	 beginTime = TimeFormat.getMonth();
			} else if(endTime == null || endTime.equals("")){				 	
			        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");			      
			        endTime = sdf.format(new Date());
				psql.append("AND TO_CHAR(S.FBIZDATE,'yyyy-MM-dd') BETWEEN '" + beginTime + "' AND '" + endTime + "'"); */
			}else{
				psql.append("AND TO_CHAR(S.FBIZDATE,'yyyy-MM-dd') BETWEEN '" + beginTime + "' AND '" + endTime + "'"); 
			}
			
			//psql.append(" ORDER BY to_char(S.FBIZDATE,'YYYY-MM-DD') DESC");
		psql.append(" ORDER BY FBIZDATE DESC");
        int psize = 50; //每页显示数
        int pindex = offset / psize;//+1  页码从0开始
        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap = subjectTwoRegService.selectPage(pindex, psql.toString(), psize);
        PageBean<SubjectTwoReg> pb = new PageBean<>();
        pb.setPc(pindex); //页码
        Map<String, Object> paramMapCount = getPageBeanCount(psql.toString()); //取记录总数
        pb.setTr((int) paramMapCount.get("Pcount")); //记录总数
        pb.setPs(psize);
        pb.setBeanList((List<SubjectTwoReg>) paramMap.get("v_cur")); //每页数据集
        return pb;
    }
	
	/**
	 * 取记录总数 dmz 20160921
	 * @param psql  SQL语句
	 * @return Map
	 * @throws Exception
	 */
	public Map<String, Object> getPageBeanCount(String psql) throws Exception{

		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap=subjectTwoRegService.selectPageCount(psql);
		return paramMap;
	}


	/**
	 * 科目二考试登记的 导出Excel
	 * @param response
	 * @param request
	 * @param fnumber
	 * @param fdescription
	 * @param studentName
	 * @param beginTime
	 * @param endTime
	 */
	@RequestMapping(value = "/importSubjectTwoReg")
	public void importSubjectTwoReg(HttpServletResponse response, HttpServletRequest request
			, @RequestParam(value = "fnumber", defaultValue = "") String fnumber
			, @RequestParam(value = "fdescription", defaultValue = "") String fdescription
			, @RequestParam(value = "studentName", defaultValue = "") String studentName
			, @RequestParam(value = "beginTime", defaultValue = "") String beginTime
			, @RequestParam(value = "endTime", defaultValue = "") String endTime){
		if("".equals(studentName)){
			studentName =null;
		}
		//获取操作人信息
		User user = (User) session.getAttribute("user");
		//定义接收类型
		List<Object> data = new ArrayList<>();
		//查询数据
		List<SubjectTwoReg> subjectTwoRegs=subjectTwoRegService.selectSubjectTwoReg(user.getFcompanyid(),fnumber,fdescription,beginTime,endTime);
		for (SubjectTwoReg subjectTwoReg :subjectTwoRegs){
			subjectTwoReg.setSubjectTwoRegentryList(subjectTwoRegentryService.selSubjectTwoRegentryByParentId(subjectTwoReg.getFid(),studentName));
			for (int i = 0; i < subjectTwoReg.getSubjectTwoRegentryList().size(); i++) {
				data.add(subjectTwoReg.getFnumber());
				data.add(subjectTwoReg.getFbizdate());
				data.add(subjectTwoReg.getFdescription());
				data.add(subjectTwoReg.getCheckName());
				data.add(subjectTwoReg.getCreateName());
				if(subjectTwoReg.getCfbillstate() == 3L){
					data.add("保存");
				}else if(subjectTwoReg.getCfbillstate() == 4L){
					data.add("提交");
				}else{
					data.add("审核");
				}
				data.add(subjectTwoReg.getSubjectTwoRegentryList().get(i).getStudentName());
				data.add(subjectTwoReg.getSubjectTwoRegentryList().get(i).getCfstudentno());
				data.add(subjectTwoReg.getSubjectTwoRegentryList().get(i).getCfidcardno());
				data.add(subjectTwoReg.getSubjectTwoRegentryList().get(i).getCfzkzmbh());
				if(subjectTwoReg.getSubjectTwoRegentryList().get(i).getCfsex() == 1L){
					data.add("男");
				}else if(subjectTwoReg.getSubjectTwoRegentryList().get(i).getCfsex() == 2L){
					data.add("女");
				}else{
					data.add("");
				}
				data.add(subjectTwoReg.getSubjectTwoRegentryList().get(i).getCfmobile());
				data.add(subjectTwoReg.getSubjectTwoRegentryList().get(i).getCfjsr());
				if(subjectTwoReg.getSubjectTwoRegentryList().get(i).getCfbdyd() == 1L){
					data.add("本地");
				}else if(subjectTwoReg.getSubjectTwoRegentryList().get(i).getCfbdyd() == 2L){
					data.add("异地");
				}else if(subjectTwoReg.getSubjectTwoRegentryList().get(i).getCfbdyd() == 3L){
					data.add("学生");
				}else{
					data.add("");
				}
				data.add(subjectTwoReg.getSubjectTwoRegentryList().get(i).getJlzname());
				data.add(subjectTwoReg.getSubjectTwoRegentryList().get(i).getCfchepai());
				data.add(subjectTwoReg.getSubjectTwoRegentryList().get(i).getCfphone());
				data.add(subjectTwoReg.getSubjectTwoRegentryList().get(i).getCfkscs());
				if(subjectTwoReg.getSubjectTwoRegentryList().get(i).getCfkscs() == 0L){
					data.add("初考");
				}else if(subjectTwoReg.getSubjectTwoRegentryList().get(i).getCfkscs() > 0L){
					data.add("补考");
				}else{
					data.add("初考");
				}
				if("1".equals(subjectTwoReg.getSubjectTwoRegentryList().get(i).getCfbxwx())){
					data.add("本校");
				}else if("2".equals(subjectTwoReg.getSubjectTwoRegentryList().get(i).getCfbxwx())){
					data.add("外校");
				}else{
					data.add("");
				}
				data.add(subjectTwoReg.getSubjectTwoRegentryList().get(i).getCfnumber());
				data.add(subjectTwoReg.getSubjectTwoRegentryList().get(i).getCfkmyjgsj());
				data.add(subjectTwoReg.getSubjectTwoRegentryList().get(i).getCfbjgsj());
				data.add(subjectTwoReg.getSubjectTwoRegentryList().get(i).getCfsqzjcxdh());
				data.add(subjectTwoReg.getSubjectTwoRegentryList().get(i).getCfzkzmbh());
				data.add(subjectTwoReg.getSubjectTwoRegentryList().get(i).getCfremark());
				data.add(subjectTwoReg.getSubjectTwoRegentryList().get(i).getCfclazz());
				data.add(subjectTwoReg.getSubjectTwoRegentryList().get(i).getCfysje());
				data.add(subjectTwoReg.getSubjectTwoRegentryList().get(i).getCfyisje());
				data.add(subjectTwoReg.getSubjectTwoRegentryList().get(i).getCfyhje());
				data.add(subjectTwoReg.getSubjectTwoRegentryList().get(i).getCfqfje());
				data.add(subjectTwoReg.getCfzbsylt());
				data.add(subjectTwoReg.getCfzbsstn());
				data.add(subjectTwoReg.getCfzbs());
				data.add(subjectTwoReg.getCfzbks());
				data.add(subjectTwoReg.getSubjectTwoRegentryList().get(i).getCfchexing());
				data.add(subjectTwoReg.getSubjectTwoRegentryList().get(i).getCfscts());
			}
		}
		//头部数据
		String head[] = {"单据编号", "业务日期", "参考信息", "审核人名称", "制单人名称", "单据状态", "学员名称", "学员编码","准考证名编号","身份证号码",
				"性别","手机","推荐人","本地/异地","教练","车牌","电话","考试次数","初考补考","本校外校"
				,"编号","科目一及格时间","科目二不及格时间","申请准驾车型代号","准考证明编号","备注","班级","已收","应收","优惠"
				,"欠费","指标数(伊兰特)","指标数(桑塔纳)","总指标数","总报考数","车型","据上次考试不及格时间天数"};
		//长度
		Integer length[] = {9000, 5000, 5000, 3000, 3000, 3000, 3000, 9000, 9000, 9000,
							3000, 3000, 3000, 3000, 9000, 3000, 3000, 3000, 3000, 3000,
							3000, 5000, 5000, 3000, 3000, 3000, 3000, 5000, 3000, 3000,
							3000, 3000, 3000, 3000, 3000, 3000, 3000};
		try {
			//导出Excel
			CommonExport.importExcel(response, "科目二考试登记" + TimeFormat.getNowTime2() + ".xls", "科目二考试登记", data, head, length);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}
